package cn.com.libertymutual.sp.dao;

import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpProduct;

@Repository
public interface ProductDao extends PagingAndSortingRepository<TbSpProduct, Integer>, JpaSpecificationExecutor<TbSpProduct> {

	// @Query("select t from TbSpProduct as t,TbSpHotproduct as h where t.id =
	// h.productId and t.isShow = '1' and h.status = '1' and t.status = '1'and
	// h.branchCode = ?1 order by t.serialNo ")
	@Query("select t from TbSpProduct t where t.status in ('1','3') and t.isExclusive=0 and t.startDate <= :today and endDate >= :today and t.id in (select distinct h.productId from TbSpHotproduct h where h.status = '1' and h.branchCode = :branchCode ) order by t.serialNo")
	List<TbSpProduct> findHotProduct(@Param("today") Date today, @Param("branchCode") String branchCode);
	@Query(" from TbSpProduct t  where t.isHot ='1' and t.status in ('1','3')")
	List<TbSpProduct> findHotPro();
	@Query("select t from TbSpProduct t where t.status in ('1','3')")
	List<TbSpProduct> findAllProduct();

	@Query("select distinct t.riskCode ,t.productType from TbSpProduct t ")
	List<String> riskCodeDistinct();

	@Transactional
	@Modifying
	@Query("update TbSpProduct set status = ?1 where id = ?2")
	int updateStatus(String status, int id);

	@Query("select t from TbSpProduct t,TbSpHotproduct h where t.id = h.productId and h.branchCode = ?1 and h.status = '1'")
	public Page<TbSpProduct> findList(@Param("branchCode") String branchCode, Pageable pageable);

	@Query("select t from TbSpProduct t where t.id in (select distinct h.productId from TbSpHotproduct h where h.status = '1' ) ")
	public Page<TbSpProduct> findAllList(Pageable pageable);

	@Query("select t from TbSpProduct t where t.id not in (select distinct h.productId from TbSpHotproduct h where h.status = '1' and h.branchCode = ?1) ")
	public Page<TbSpProduct> findAreaNoHotList(String branchCode, Pageable pageable);

	@Transactional
	@Modifying
	@Query("update TbSpProduct set serialNo = ?2 where id = ?1")
	void updateProductSerialNo(Integer id, Integer serialNo);

	@Query(" from TbSpProduct t  where t.id =?1 and t.status in ('1','3')")
	TbSpProduct findByIdYes(int string);

	@Query(" from TbSpProduct t  where t.id =?1 and t.status in ('1')")
	TbSpProduct findByShowId(Integer id);

	@Query(value = "SELECT t.* from tb_sp_product t where status = ?1 order by ?#{#pageable}", countQuery = "select count(*) from tb_sp_product where status = ?1 ", nativeQuery = true)
	Page<TbSpProduct> findProductList(String status, Pageable pageable);

	@Query(value = "SELECT * from (SELECT t.* from tb_sp_product t,tb_sp_exclusive_product h where t.ID = h.PRODUCT_ID and h.USER_CODE = :userCode and t.STATUS in ('1','3') ORDER BY t.SERIAL_NO ) t1 union SELECT * from (select tsp.* from tb_sp_product tsp where tsp.STATUS in ('1','3') and tsp.IS_EXCLUSIVE ='0' ORDER BY tsp.SERIAL_NO) t2 ORDER BY SERIAL_NO limit :pageNumber , :pageSize",nativeQuery = true)
	List<TbSpProduct> findChannelProductList(@Param("userCode")String userCode,@Param("pageNumber") Integer pageNumber, @Param("pageSize") Integer pageSize);

	@Query(value = "SELECT * from (SELECT t.* from tb_sp_product t,tb_sp_hotproduct h where t.ID = h.PRODUCT_ID and t.START_DATE <=:today and t.END_DATE >=:today  and  h.BRANCH_CODE = :branchCode and h.STATUS= '1' and t.STATUS in ('1','3') and t.IS_EXCLUSIVE =0 ORDER BY t.SERIAL_NO ) t1 union  SELECT * from (select tsp.* from tb_sp_product tsp where tsp.STATUS in ('1','3') and tsp.IS_EXCLUSIVE =0 and tsp.START_DATE <=:today and tsp.END_DATE >=:today ORDER BY tsp.SERIAL_NO) t2 limit :pageNumber , :pageSize", nativeQuery = true)
	List<TbSpProduct> findBranchHotProductList(@Param("today") String today, @Param("branchCode") String branchCode,
			@Param("pageNumber") Integer pageNumber, @Param("pageSize") Integer pageSize);

	@Query(value = "select count(*) from tb_sp_product where STATUS in ('1','3') and START_DATE <=:today and END_DATE >=:today and IS_EXCLUSIVE =0", nativeQuery = true)
	Integer findTotalProduct(@Param("today") String today);

	@Query("select t from TbSpProduct t where t.status in ('1','3') and t.riskCode = ?1 order by t.serialNo ")
	List<TbSpProduct> findByRiskCode(String riskCode);

	@Transactional
	@Modifying
	@Query("update TbSpProduct set isExclusive = ?1 where id = ?2")
	void setExclusive(String Exclusive, Integer productId);

	Optional<TbSpProduct> findById(Integer id);

	@Query("select t from TbSpProduct t where t.status in ('1','3') and id in (:ids) order by id DESC")
	List<TbSpProduct> findInIds(@Param("ids") List<Integer> ids);

}
